package db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbcp.BasicDataSource;

import util.FenYe;
import util.Log;
import util.Page;

@SuppressWarnings("unchecked")
public class JdbcOperate {
	private static Connection con;
	static BasicDataSource bs = new BasicDataSource();
	
	static{
		bs.setUrl("jdbc:oracle:thin:@localhost:1521:orcl");
		bs.setUsername("scott");
		bs.setPassword("tiger");
		bs.setDriverClassName("oracle.jdbc.driver.OracleDriver");
	
		bs.setMaxWait(1);
		bs.setMaxActive(11);
		bs.setMaxIdle(1);
	}
	public static Object execute(CallStatement cs) throws SQLException {
		Object obj = null;
		Statement stm = null;
		try {
			con.setAutoCommit(false);
			stm = con.createStatement();
			obj = cs.doinStatement(stm);
			con.commit();
		} finally {
			close(con, stm, null);
		}
		return obj;
	}

	public static List<Map> query(String SQL) throws SQLException {
		Log.write(1, null, "sql执行语句：" + SQL, null);
		List list = new ArrayList();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SQL);
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map map = new HashMap();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					map.put(rsmd.getColumnName(i), rs.getObject(i) == null ? ""
							: rs.getObject(i).toString());
				}
				list.add(map);
			}
		} finally {
			close(conn, stmt, rs);
		}
		return list;
	}

	public static Boolean insertOrUpdate(String SQL) throws SQLException {
		Log.write(1, null, "sql执行语句：" + SQL, null);
		Connection con = null;
		Statement stm = null;
		Boolean result = false;
		try {
			con = openConnection();
			stm = con.createStatement();
			result = stm.execute(SQL);
		} finally {
			close(con, stm, null);
		}
		return result;

	}

	public static void batchInsertOrUpdate(String SQLs) throws SQLException {
		Connection con = null;
		Statement stm = null;
		try {
			String[] sqls = SQLs.split(";");
			con = openConnection();
			con.setAutoCommit(false);
			stm = con.createStatement();
			for (String sql : sqls) {
				stm.addBatch(sql);
				Log.write(1, null, "" + sql, null);
			}
			stm.executeBatch();
			con.commit();
		} finally {
			close(con, stm, null);
		}
	}

	public static boolean delete(String Table, String field, String value) throws SQLException {
		Connection con = null;
		Statement stm = null;
		Boolean result = false;
		try {
			con = openConnection();
			stm = con.createStatement();
			StringBuffer sb=new StringBuffer();
			sb.append("update "+Table+" t set t.isdelete='0' where t.");
			sb.append(field+"="+value);
			Log.write(1, null, "sql执行语句：" + sb.toString(), null);
			result = stm.execute(sb.toString());
		} finally {
			close(con, stm, null);
		}
		return result;

	}

	public static Page getPageNew(Page page, String sql) throws SQLException {
		if (page == null) {
			page = new Page();
		}
		String countSQL = FenYe.getCount(sql);
		List<Map> countList = query(countSQL);
		int count = Integer.valueOf(countList.get(0).get("COUNT").toString());
		page.setVisitCount(count);
		String pageSQL = FenYe.getPage(sql, page);
		List<Map> list = query(pageSQL);
		page.setPageList(list);
		return page;
	}

	/**@Desc根据序列名称返回一个序列id
	 * @param seqName序列名称
	 * @return
	 * @throws SQLException
	 */
	public static String getSeq(String seqName) throws SQLException {
		String seq = "select " + TableConstants.FORM_SEQ
				+ ".nextval as seq FROM dual";
		List<Map> list = JdbcOperate.query(seq.toString());
		return list.get(0).get("SEQ").toString();
	}
	public static Connection openConnection() {
	
		Connection con = null;
		
		
		Log.debug("活动"+bs.getNumActive());
		Log.debug("空闲"+bs.getNumIdle());

		try {
			con = bs.getConnection();
		} catch (SQLException e) {
			Log.write(3, JdbcOperate.class, "", e);
		} catch (Exception e) {
			Log.write(3, JdbcOperate.class, "", e);
		}
		return con;
	}

	private static void close(Connection con, Statement stm, ResultSet rs) {
		try {
			if (con != null) {
				con.close();
			}
			if (stm != null) {
				stm.close();
			}
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			Log.write(3, JdbcOperate.class, "", e);
		}
	}

}
